package interchange.data;

import interchange.domain.Login;
import interchange.domain.Usuario;
import interchange.utiles.UIDGenerator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;




public class JDBCUsuarioDAO implements IUsuarioDAO {

	private Connection conn;
	ILoginDAO idao;
	
    public JDBCUsuarioDAO() {
        conn = ConnectionManager.getInstance().checkOut();
        idao = new JDBCLoginDAO();
        
    }
    
    protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }

	
	public void delete(Connection conn, String IDUsuario) {
		String sql = "DELETE FROM Usuario WHERE (OID = ?) ";
        PreparedStatement stmt = null;

        String OIDUsuario = selectOIDOfUsuario(IDUsuario);
        if (OIDUsuario!=null) {
            
            String OIDLogin = selectOIDOfLogin(OIDUsuario);


            idao.delete(conn, OIDLogin);


            try {
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, IDUsuario);
                stmt.executeUpdate();
            } catch (SQLException e) {
                System.out.println("Message: " + e.getMessage());
                System.out.println("SQLState: " + e.getSQLState());
                System.out.println("ErrorCode: " + e.getErrorCode());
            } finally {
                try {
                    if (stmt != null)
                        stmt.close();
                } catch (SQLException e) {
                }
            }
            }

        }



	private String selectOIDOfLogin(String OIDUsuario) {
		PreparedStatement stmt = null;
	    ResultSet result = null;
	    String oidLogin = null;
	    String sql = "SELECT * FROM Usuario WHERE (OIDUsuario = ?) ";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, OIDUsuario);
	            result = stmt.executeQuery();

	            if (result.next()) {
	                oidLogin = result.getString("OIDLogin");
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return oidLogin;
	}

	private String selectOIDOfUsuario(String iDUsuario) {
		
	    PreparedStatement stmt = null;
	    ResultSet result = null;
	    String oid = null;
	    String sql = "SELECT * FROM Usuario WHERE (OIDUsuario = ?) ";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, iDUsuario);
	            result = stmt.executeQuery();

	            if (result.next()) {
	                oid = result.getString("OIDUsuario");
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return oid;
	}

	
	public void insert(Connection conn, String OIDUsuario, Usuario a) {
	
	
		String oidu = UIDGenerator.getInstance().getKey();
		
		String oidl = UIDGenerator.getInstance().getKey();
        idao.insert(conn, oidl, a.getLogin());

		String sql = "INSERT INTO Usuario ( OIDUsuario,OIDLogin, idUsuario,nombre, apellidos, email, dni, telefono, direccion, codigopostal, provincia, localidad, puntuacion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
	
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, oidu);
            stmt.setString(2, oidl);
            stmt.setString(3, a.getUsuarioID());
            stmt.setString(4, a.getNombre());
            stmt.setString(5, a.getApellidos());
            stmt.setString(6, a.getEmail());
            stmt.setString(7, a.getDni());
            stmt.setInt(8, a.getTelefono());
            stmt.setString(9,a.getDireccion());
            stmt.setInt(10, a.getCp());
            stmt.setString(11,a.getProvincia());
            stmt.setString(12,a.getLocalidad());
            stmt.setDouble(13,a.getPuntuacion());
            

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

	}

	
	public Usuario select(Connection conn, String OIDUsuario) {
		  String sql = "SELECT * FROM Usuario WHERE (OIDUsuario = ? ) ";
	        PreparedStatement stmt = null;
	        ResultSet result = null;
	        Usuario u = null;

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, OIDUsuario);

	            result = stmt.executeQuery();

	            result.next();
	            String oidu=result.getString("OIDLogin");
	            u = new Usuario();
	            u.setNombre(result.getString("nombre"));
	            u.setApellidos(result.getString("apellidos"));
	            u.setCp(new Integer(result.getString("codigoPostal")));
	            u.setDireccion(result.getString("direccion"));
	            u.setDni(result.getString("dni"));
	            u.setEmail(result.getString("email"));
	            u.setLocalidad(result.getString("localidad"));
	            u.setProvincia(result.getString("provincia"));
	            u.setTelefono(new Integer(result.getString("telefono")));
	            u.setPuntuacion(new Double(result.getString("puntuacion")));
	            u.setLogin((Login) idao.select(conn,oidu));
	            
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }

	        return u;
	    }
	}

